package crm.control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import crm.util.Pagination;
import crm.dbcoon.DBConnect;
import crm.model.Purchase;

/**
 * @author zengh
 * @time 2012-7-19
 * @content 采购
 */
public class PurchaseControl {
	public static Pagination pageInstance = null;
	/**
	 * @function 插入采购记录
	 * @param p
	 * @return id
	 */
	public static int addPurchase(Purchase p){
		DBConnect dbConnect = new DBConnect();
		String sqlString = "insert into control_purchase(goods_name,single_price,goods_num,sum_fee,place,time,person," +
				"seller,seller_contact,recepit_id) value(?,?,?,?,?,?,?,?,?,?)";
		int id= 0;
		ResultSet rsResultSet = null;
		dbConnect.prepareStatement(sqlString);
		try{
			dbConnect.setString(1, p.getGoods_name());
			dbConnect.setFloat(2, (float)p.getSingle_price());
			dbConnect.setInt(3, p.getGoods_num());
			dbConnect.setFloat(4,(float)p.getSum_fee());
			dbConnect.setString(5, p.getPlace());
			dbConnect.setString(6, p.getTime());
			dbConnect.setString(7, p.getPerson());
			dbConnect.setString(8, p.getSeller());
			dbConnect.setString(9, p.getSeller_contact());
			dbConnect.setInt(10, p.getRecepit_id());
			dbConnect.executeUpdate();
			rsResultSet = dbConnect.getPreparedStatement().getGeneratedKeys();
			if(rsResultSet.next()){
				id = rsResultSet.getInt(1);
			}
			return id;
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("addPurchase error!");
			return id;
		}finally{
			dbConnect.close();
		}
	}
	/**
	 * @function 根据id删除一条采购记录
	 * @param id
	 * @return true or false
	 */
	public static boolean deletePurchase(int id){
		DBConnect dbConnect = new DBConnect();
		String sqlString = "delete from control_purchase where id="+id;
		dbConnect.prepareStatement(sqlString);
		try{
			dbConnect.executeUpdate();
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("deletePurchase error!");
			return false;
		}finally{
			dbConnect.close();
		}
	}
	/**
	 * @function 根据修改采购记录的单价、物品数量、总花销
	 * @param p
	 * @param id
	 * @return true or false
	 */
	public static boolean updatePurchase(Purchase p,int id){
		DBConnect dbConnect = new DBConnect();
		String sqlString = "update control_purchase set single_price=?,goods_num=?,sum_fee=?,goods_name=? ,place=?,time=?,person=?,seller=?,seller_contact=? where id="+id;
		dbConnect.prepareStatement(sqlString);
		try{
			dbConnect.setFloat(1, (float)p.getSingle_price());
			dbConnect.setInt(2, p.getGoods_num());
			dbConnect.setFloat(3,(float)p.getSum_fee());
			dbConnect.setString(4, p.getGoods_name());
			dbConnect.setString(5, p.getPlace());
			dbConnect.setString(6, p.getTime());
			dbConnect.setString(7, p.getPerson());
			dbConnect.setString(8, p.getSeller());
			dbConnect.setString(9, p.getSeller_contact());
			dbConnect.executeUpdate();
			System.out.println(sqlString);
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("updatePurchase error!");
			return false;
		}finally{
			dbConnect.close();
		}		
	}
	/**
	 * @function 根据ID获取采购记录的信息
	 * @param id
	 * @return 一条采购记录
	 */
	public static Purchase getPurchaseById(int id){
			DBConnect dbc = new DBConnect();
			String sqlString = "select * from control_purchase where id="+id;
			dbc.prepareStatement(sqlString);
			try{
				Purchase purchase = new Purchase();
				ResultSet rsResultSet = dbc.executeQuery();
				if(rsResultSet.next()){
					getByRs(purchase, rsResultSet);
					return purchase;
				}
			}catch(SQLException e){
				e.printStackTrace();
				System.out.println("getPurchase error!");
				return null;
			}finally{
				dbc.close();
			}
			return null;
		}
	/**
	 * @function 根据商品名或购买者或购买地点或购买商搜索采购记录
	 * @param goods_name
	 * @param person
	 * @param place
	 * @param seller
	 * @param rowsPerPage
	 * @param page
	 * @return
	 * @throws SQLException
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public static Collection searchPurchase(String recepit_id,String goods_name,String person,String place,String seller,int rowsPerPage,int page) {
		if(recepit_id==null||recepit_id.equals("")){
			recepit_id="%";
		}
		if(goods_name==null||goods_name.equals("")){
			goods_name="%";
		}
		if(person==null||person.equals("")){
			person="%";
		}
		if(place==null||place.equals("")){
			place="%";
		}
		if(seller==null||seller.equals("")){
			seller="%";
		}
		String sqlString="";
		if(recepit_id.equals("-1")){
			sqlString = "select * from control_purchase where goods_name like '%"+goods_name+"%' and person like '%"+person+"%' and place like '%"+place+"%' and seller like '%"+seller+"%'";
		}else if(recepit_id.equals("-2")){		
		    sqlString = "select * from control_purchase where id not in (select id from control_purchase where recepit_id='0')";
		}else{
			sqlString = "select * from control_purchase where recepit_id like '"+recepit_id+"' and goods_name like '%"+goods_name+"%' and person like '%"+person+"%' and place like '%"+place+"%' and seller like '%"+seller+"%'";
		}
		System.out.println(sqlString);
		pageInstance = new PurchaseControlPage(sqlString, rowsPerPage);
	   try{
		   return pageInstance.getPage(page);
	   }catch(Exception e){
		   e.printStackTrace();
		   return null;
	   }
	}
	/**
	 * @function
	 * @param p
	 * @param rs
	 */
	public static void getByRs(Purchase p,ResultSet rs){
		try{
			p.setId(rs.getInt("id"));
			p.setGoods_name(rs.getString("goods_name"));
			p.setSingle_price(rs.getDouble("single_price"));
			p.setGoods_num(rs.getInt("goods_num"));
			p.setSum_fee(rs.getDouble("sum_fee"));
			p.setPlace(rs.getString("place"));
			p.setTime(rs.getString("time"));
			p.setPerson(rs.getString("person"));
			p.setSeller(rs.getString("seller"));
			p.setSeller_contact(rs.getString("seller_contact"));
			p.setRecepit_id(rs.getInt("recepit_id"));
			//System.out.println(p.getSingle_price());
		}catch (SQLException e) {
			e.printStackTrace();
			System.out.println("getByRs error!");
		}
	}
	/**
	 * @function 主方法，用来测试addPurchase、updatePurchase、getPurchaseById、deletePurchase是否好用
	 * @param args
	 */
	//public static void main(String args[]){
		//Purchase purchase = new Purchase();
		/*purchase.setGoods_name("test");
		purchase.setGoods_num(50);
		purchase.setPerson("test");
		purchase.setPlace("test");
		purchase.setSeller("test");
		purchase.setSeller_contact("110");
		purchase.setSingle_price(22.0);
		purchase.setSum_fee(1000);
		purchase.setTime("2012-7-20");
		if(addPurchase(purchase)!=0){
			System.out.println("success!");
		}else {
			System.out.println("failed!");
		}*/
		
		/*purchase.setGoods_num(45);
		if(updatePurchase(purchase,1)){
			System.out.println("success!");
		}else {
			System.out.println("failed!");
		}*/
		
		/*purchase = getPurchaseById(1);
		System.out.println(purchase.getGoods_name());*/
		
		//deletePurchase(1);
	//}
	/*public Collection getPurchase(int pages,int rowsPerpage,String time)
	throws SQLException,Exception{
		String sqlString = "select * from control_purchase order by "+time+" desc";
		pageInstance = new PurchaseControlPage(sqlString, rowsPerpage);
		return pageInstance.getPage(pages);
	}*/

}
/**
 * @function分页显示
 */
class PurchaseControlPage extends Pagination{
	public PurchaseControlPage(String sql,int rowsPerPage){
		super(sql, rowsPerPage, 1);
	}
	protected Collection<Purchase> packResultSet(ResultSet rs){
		Collection<Purchase> plist= new ArrayList<Purchase>();
		try{
		while(rs.next()){
			Purchase purchase = new Purchase();
			PurchaseControl.getByRs(purchase, rs);
			plist.add(purchase);
			purchase = null;
		}
		}catch (Exception e) {
			e.printStackTrace();
		}
		return plist;
	}
	}
